﻿CREATE FUNCTION [dbo].[ratings_get_book_rating](@bookId int, @userId int = 0)
RETURNS FLOAT
AS
BEGIN
    DECLARE @storyRate FLOAT = dbo.ratings_get_rate(
        (SELECT Sum(Ratings.Story) * 1.0 From Ratings Where Ratings.BookId = @bookId AND Ratings.Story IS NOT NULL),
        (SELECT Count(1) From Ratings Where Ratings.BookId = @bookId AND Ratings.Story IS NOT NULL));
    DECLARE @heroesRate FLOAT = dbo.ratings_get_rate(
        (SELECT Sum(Ratings.Heroes) * 1.0 From Ratings Where Ratings.BookId = @bookId AND Ratings.Heroes IS NOT NULL),
        (SELECT Count(1) From Ratings Where Ratings.BookId = @bookId AND Ratings.Heroes IS NOT NULL));
    DECLARE @rereadingRate FLOAT = dbo.ratings_get_rate(
        (SELECT Sum(Ratings.Rereading) * 1.0 From Ratings Where Ratings.BookId = @bookId AND Ratings.Rereading IS NOT NULL),
        (SELECT Count(1) From Ratings Where Ratings.BookId = @bookId AND Ratings.Rereading IS NOT NULL));

    DECLARE @sumRate FLOAT = 0;
    DECLARE @countRates INT = 0;
    IF EXISTS (SELECT * FROM RatingPreference WHERE RatingPreference.UserId = @userId)
    BEGIN
        DECLARE @preferenceStoryRate INT = (SELECT RatingPreference.Story FROM RatingPreference WHERE RatingPreference.UserId = @userId);
        DECLARE @preferenceHeroesRate INT = (SELECT RatingPreference.Heroes FROM RatingPreference WHERE RatingPreference.UserId = @userId);
        DECLARE @preferenceRereadingRate INT = (SELECT RatingPreference.Rereading FROM RatingPreference WHERE RatingPreference.UserId = @userId);
        DECLARE @maxPreferenceVote INT = (SELECT MAX(X) FROM(VALUES(@preferenceStoryRate), (@preferenceHeroesRate), (@preferenceRereadingRate)) T(X));
        DECLARE @sumPreferenceVote INT = @preferenceStoryRate + @preferenceHeroesRate + @preferenceRereadingRate;

        RETURN (@storyRate * @preferenceStoryRate +
               @heroesRate * @preferenceHeroesRate +
               @rereadingRate * @preferenceRereadingRate) / @sumPreferenceVote;
    END
    ELSE 
    BEGIN
        IF @storyRate <> 0
        BEGIN
            SET @sumRate = @sumRate + @storyRate
            SET @countRates = @countRates + 1
        END
        IF @heroesRate <> 0
        BEGIN
            SET @sumRate = @sumRate + @heroesRate
            SET @countRates = @countRates + 1
        END
        IF @rereadingRate <> 0
        BEGIN
            SET @sumRate = @sumRate + @rereadingRate
            SET @countRates = @countRates + 1
        END
    END

    RETURN CASE WHEN @countRates <> 0 THEN @sumRate / @countRates ELSE 0 END
END

